package cm.tl.pms.util.fileParser.excel;


import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Slf4j
public class ExcelParser {


    @Data
    @AllArgsConstructor
    @NoArgsConstructor
    public static class headerData {
        /**
         * 客户名称
         */
        private String projectName;
        /**
         * 业务品种
         */
        private String businessType;
        /**
         * 余额（元）
         */
        private String loanMoney;
        /**
         * 流水号
         */
        private String serialNo;
        /**
         * 公司代码
         */
        private String companyNo;
        /**
         * 管理主体
         */
        private String manage;
        /**
         * 币种
         */
        private String currency;
        /**
         * 担保类型
         */
        private String guaranteeTypes;
        /**
         * 担保单位
         */
        private String guaranteeUnit;
        /**
         * 担保金额(元)
         */
        private String guaranteeMoney;
        /**
         * 抵押人
         */
        private String pledger;
        /**
         * 抵押金额（元）
         */
        private String pledgeMoney;
        /**
         * 五级分类结果
         */
        private String level;
        /**
         * 部门
         */
        private String dept;
        /**
         * 客户所处行业
         */
        private String industry;
    }

    // 定义一个类来存储贷款数据
    @Data
    @AllArgsConstructor
    @NoArgsConstructor
    public static class LoanData {
        private String projectName; // 客户名称
        private String businessType; // 业务品种
        private String loanMoney; // 余额（元）
    }

    // 新的实体类，封装解析结果
    @Data
    @AllArgsConstructor
    @NoArgsConstructor
    public static class ParseResult<T> {
        private List<T> dataList;
        private List<Map<String, String>> fieldValuesList;
    }

    public static void main(String[] args) {
        // 由于文件已经上传，我们将使用上传文件的路径
//        String filePath = "C://Users//Lenovo//Desktop//核心数据抓取表格(1).xlsx";
//        String filePath = "D:\\Java\\apache-tomcat-8.0.53\\webapps\\file\\cn_loan\\172975371927688183.xlsx";
        Map<String, String> columnToPropertyMap = new HashMap<>();
        columnToPropertyMap.put("客户名称", "projectName");
        columnToPropertyMap.put("业务品种", "businessType");
        columnToPropertyMap.put("余额（元）", "loanMoney");

        // 解析Excel并获取数据列表
//        ParseResult<LoanData> parseResult = parseExcel(columnToPropertyMap, LoanData.class, filePath);
//        parseResult.getDataList().forEach(System.out::println);
//
//        parseResult.getFieldValuesList().forEach(System.out::println);


        Test();
    }

    public static void Test() {
//        String filePath = "D:\\Java\\apache-tomcat-8.0.53\\webapps\\file\\cn_loan\\172975371927688183.xlsx";
        String filePath = "C:\\Users\\Lenovo\\Desktop\\核心数据抓取表格1026.xlsx";

        Map<String, String> columnToPropertyMap = new HashMap<>();
        columnToPropertyMap.put("客户名称", "projectName");
        columnToPropertyMap.put("业务品种", "businessType");
        columnToPropertyMap.put("余额（元）", "loanMoney");
//        columnToPropertyMap.put("流水号", "serialNo");
        columnToPropertyMap.put("公司代码", "companyNo");
//        columnToPropertyMap.put("管理主体", "manage");
//        columnToPropertyMap.put("币种", "currency");
//        columnToPropertyMap.put("担保类型", "guaranteeTypes");
//        columnToPropertyMap.put("担保单位", "guaranteeUnit");
//        columnToPropertyMap.put("担保金额(元)", "guaranteeMoney");
//        columnToPropertyMap.put("抵押人", "pledger");
//        columnToPropertyMap.put("抵押金额（元）", "pledgeMoney");
//        columnToPropertyMap.put("五级分类结果", "level");
//        columnToPropertyMap.put("部门", "dept");
//        columnToPropertyMap.put("客户所处行业", "industry");

        // 解析Excel并获取数据列表
//        ParseResult<Projects> parseResult = parseExcel(columnToPropertyMap, Projects.class, filePath);
//        parseResult.getDataList().forEach(System.out::println);
//
//        parseResult.getFieldValuesList().forEach(System.out::println);
//
//        List<Projects> projectsList = parseResult.getDataList();
//
//        List<Projects> mergedProjects = ObjectListMerger.merge(projectsList, Projects.class, "projectName", ",");
//        System.out.println("---------------------");
//        mergedProjects.forEach(System.out::println);
    }

    public static <T> ParseResult<T> parseExcel(Map<String, String> columnToPropertyMap, Class<T> clazz, String filePath) {
        log.info("开始解析Excel文件：" + filePath);
        List<T> dataList = new ArrayList<>();
        List<Map<String, String>> fieldValuesList = new ArrayList<>();

        try (FileInputStream fis = new FileInputStream(new File(filePath));
             Workbook workbook = new XSSFWorkbook(fis)) {
            Sheet sheet = workbook.getSheetAt(0);
            Row headerRow = sheet.getRow(2); // 假设标题行是第3行（索引为2）

            // 寻找列索引
            Map<String, Integer> columnIndices = new HashMap<>();
            for (int i = 0; i < headerRow.getLastCellNum(); i++) {
                Cell cell = headerRow.getCell(i);
//                System.out.println("+++++++++++++" + cell);
                if (cell != null) {
//                    String header =  cell.getStringCellValue();
                    String header = getCellStringValue(cell); // 转化字符串处理
                    //找到 列头对应的索引map
                    columnIndices.put(header, i);
                }
            }

            // 检查所有需要的列是否都存在
            boolean allColumnsExist = true;
            for (String requiredColumn : columnToPropertyMap.keySet()) {
                if (!columnIndices.containsKey(requiredColumn)) {
                    System.out.println("缺少列: " + requiredColumn);
                    allColumnsExist = false;
                }
            }


            if (!allColumnsExist) {
                boolean allColumnsAbsent = columnToPropertyMap.keySet().stream().noneMatch(columnIndices::containsKey);
                if (allColumnsAbsent) {
                    log.warn("Excel文件缺少所有列，无法解析!");
                    return new ParseResult<>(new ArrayList<>(), new ArrayList<>());
                }
            }

            // 读取数据
            for (int i = 3; i <= sheet.getLastRowNum(); i++) { // 假设数据从第4行开始（索引为3）
                Row row = sheet.getRow(i);
                if (row != null) {
                    Map<String, String> fieldValueMap = new HashMap<>();
                    boolean hasNullValue = false;

                    for (Map.Entry<String, String> entry : columnToPropertyMap.entrySet()) {
                        String columnName = entry.getKey();
                        if (!columnIndices.containsKey(columnName)) {
                            continue;
                        }
                        int columnIndex = columnIndices.get(columnName);
                        Cell cell = row.getCell(columnIndex);
//                        System.out.println("Processing cell at row " + i + ", column " + columnIndex + " with type " + (cell == null ? "null" : cell.getCellType())); // 调试输出

                        String cellValue = getCellStringValue(cell);

                        if (cellValue == null) {
                            hasNullValue = true;
                            break;
                        }

                        fieldValueMap.put(columnName, cellValue);
                    }

                    if (!hasNullValue) {
                        fieldValuesList.add(fieldValueMap);
                        T data = createDataFromMap(fieldValueMap, columnToPropertyMap, clazz);
                        if (data != null) {
                            dataList.add(data);
                        }
                    }
                }
            }

            return new ParseResult<>(dataList, fieldValuesList);
        } catch (IOException e) {
            e.printStackTrace();
            return new ParseResult<>(dataList, fieldValuesList);
        }
    }


    public static <T> List<Map<String, String>> getFieldValues(ParseResult<T> parseResult) {
        return parseResult.getFieldValuesList();
    }

    // 从Map中创建数据对象
    private static <T> T createDataFromMap(Map<String, String> fieldValueMap, Map<String, String> columnToPropertyMap, Class<T> clazz) {
        try {
            T data = clazz.getDeclaredConstructor().newInstance();
            for (Map.Entry<String, String> entry : fieldValueMap.entrySet()) {
                String columnName = entry.getKey();
                String cellValue = entry.getValue();
                setFieldValue(data, columnToPropertyMap.get(columnName), cellValue);
            }
            return data;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    // 设置对象字段的值
    private static void setFieldValue(Object obj, String fieldName, String value) {
        try {
            Field field = obj.getClass().getDeclaredField(fieldName);
            field.setAccessible(true);
            field.set(obj, value);
        } catch (NoSuchFieldException | IllegalAccessException e) {
            e.printStackTrace();
        }
    }

    // 获取单元格的字符串值
//    private static String getCellStringValue(Cell cell) {
//        if (cell == null) {
//            return null;
//        }
//        switch (cell.getCellType()) {
//            case STRING:
//                return cell.getStringCellValue();
//            case NUMERIC:
//                if (DateUtil.isCellDateFormatted(cell)) {
//                    return cell.getLocalDateTimeCellValue().toString();
//                } else {
//                    return Double.toString(cell.getNumericCellValue());
//                }
//            case BOOLEAN:
//                return Boolean.toString(cell.getBooleanCellValue());
//            case FORMULA:

    /// /                return getCellStringValue(cell.getCachedFormulaResult());
//                return "";
//            default:
//                return null;
//        }
//    }

// 获取单元格的字符串值
    private static String getCellStringValue(Cell cell) {
        if (cell == null) {
            return null;
        }
//        System.out.println("Cell type: " + cell.getCellType()); // 调试输出
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getLocalDateTimeCellValue().toString();
                } else {
                    // 将数值转换为字符串
                    return String.valueOf(cell.getNumericCellValue());
                }
            case BOOLEAN:
                return Boolean.toString(cell.getBooleanCellValue());
            case FORMULA:
                // 计算公式结果并返回字符串值
                return String.valueOf(cell.getCachedFormulaResultType());
            default:
                return null;
        }
    }


}
